--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_ctr_remdat AS (SELECT * FROM &erp.ctr_remdat WHERE 1=2);

/*
select * from (
SELECT &outils.stragg(source) source,remdat_id, devise_cod, remcod_cod, promo_cod, enreg_id, marque_cod, client_cod, enreg_s, debut_dat, fin_dat, debliv_dat, finliv_dat
FROM (
  SELECT 'uv' source ,g.* FROM se_ctr_remdat g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ctr_remdat g
  )
GROUP BY remdat_id, devise_cod, remcod_cod, promo_cod, enreg_id, marque_cod, client_cod, enreg_s, debut_dat, fin_dat, debliv_dat, finliv_dat
HAVING Count(1)!=2) r
ORDER BY remcod_cod,debut_dat*/


DELETE FROM se_ctr_remdat;

INSERT INTO se_ctr_remdat
  (SELECT 0 remdat_id,
          'EUR' devise_cod,
          'RLA' remcod_cod,
          NULL promo_cod,
          0 enreg_id,
          1 marque_cod,
          SubStr(id,1,5) client_cod,
          'N' enreg_s,
          '01/01/2015' debut_dat,
          NULL fin_dat,
          NULL debliv_dat,
          NULL finliv_dat

  FROM uv_cmarque_client
  WHERE remfac IS NOT NULL  )   ;


 UPDATE se_ctr_remdat tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;

 UPDATE se_ctr_remdat tf SET remdat_id=Nvl((select remdat_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET remdat_id=&outils.ctr_remdat.NEXTVAL WHERE remdat_id IS NULL OR remdat_id =0 ;


 CALL uvse_synchroverserp('remdat_id','CTR_REMDAT','O') ;

 commit;






DELETE FROM se_ctr_remdat;

INSERT INTO se_ctr_remdat
  (SELECT DISTINCT 0 remdat_id,
          'EUR' devise_cod,
          'ARTA' remcod_cod,
          NULL promo_cod,
          0 enreg_id,
          Nvl(MARQUE,1) marque_cod,
          client client_cod,
          'N' enreg_s,
          DATDEB2 debut_dat,
          Max(DATFIN2) fin_dat,
          NULL debliv_dat,
          NULL finliv_dat

  FROM (
  SELECT r.*,To_Date('31/12/1967','dd/mm/yyyy')+db.datdeb2 datdeb2,To_Date('31/12/1967','dd/mm/yyyy')+df.datfin2 datfin2,t.taux2
  FROM uv_cremartp r,uv_cremartp_datdeb2 db,uv_cremartp_datfin2 df, uv_cremartp_taux2 t
  WHERE db.id=r.id
    AND df.id=db.id AND df.rangmulti2=db.rangmulti2
    AND df.id=t.id AND df.rangmulti2=t.rangmulti2
    AND r.cclasse IS null
  )
  GROUP BY  Nvl(MARQUE,1),client,DATDEB2)   ;


 UPDATE se_ctr_remdat tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;

 UPDATE se_ctr_remdat tf SET remdat_id=Nvl((select remdat_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET remdat_id=&outils.ctr_remdat.NEXTVAL WHERE remdat_id IS NULL OR remdat_id =0 ;

 CALL uvse_synchroverserp('remdat_id','CTR_REMDAT','O') ;

 commit;






DELETE FROM se_ctr_remdat;

INSERT INTO se_ctr_remdat
  (SELECT DISTINCT 0 remdat_id,
          'EUR' devise_cod,
          'LIGA' remcod_cod,
          NULL promo_cod,
          0 enreg_id,
          Nvl(MARQUE,1) marque_cod,
          client client_cod,
          'N' enreg_s,
          DATDEB2 debut_dat,
          Max(DATFIN2) fin_dat,
          NULL debliv_dat,
          NULL finliv_dat

  FROM (
  SELECT r.*,To_Date('31/12/1967','dd/mm/yyyy')+db.datdeb2 datdeb2,To_Date('31/12/1967','dd/mm/yyyy')+df.datfin2 datfin2,t.taux2
  FROM uv_cremartp r,uv_cremartp_datdeb2 db,uv_cremartp_datfin2 df, uv_cremartp_taux2 t
  WHERE db.id=r.id
    AND df.id=db.id AND df.rangmulti2=db.rangmulti2
    AND df.id=t.id AND df.rangmulti2=t.rangmulti2
    AND r.cclasse IS not null
  )
  GROUP BY  Nvl(MARQUE,1),client,DATDEB2)   ;


 UPDATE se_ctr_remdat tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;

 UPDATE se_ctr_remdat tf SET remdat_id=Nvl((select remdat_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET remdat_id=&outils.ctr_remdat.NEXTVAL WHERE remdat_id IS NULL OR remdat_id =0 ;

 CALL uvse_synchroverserp('remdat_id','CTR_REMDAT','O') ;

 commit;




DELETE FROM se_ctr_remdat;

INSERT INTO se_ctr_remdat
  (SELECT DISTINCT 0 remdat_id,
          'EUR' devise_cod,
          'RPA' remcod_cod,
          NULL promo_cod,
          0 enreg_id,
          Nvl(MARQUE,1) marque_cod,
          client client_cod,
          'N' enreg_s,
          '01/01/2015' debut_dat,
          null fin_dat,
          NULL debliv_dat,
          NULL finliv_dat

  FROM (
  SELECT r.*,s.seuil,t.taux
  FROM uv_cremqp r,uv_cremqp_seuil s, uv_cremqp_taux t
  WHERE s.id=r.id
    AND t.id=s.id AND t.rangmulti2=s.rangmulti2

    )
  )   ;


 UPDATE se_ctr_remdat tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;

 UPDATE se_ctr_remdat tf SET remdat_id=Nvl((select remdat_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET remdat_id=&outils.ctr_remdat.NEXTVAL WHERE remdat_id IS NULL OR remdat_id =0 ;

 CALL uvse_synchroverserp('remdat_id','CTR_REMDAT','O') ;

 commit;





DELETE FROM se_ctr_remdat;

INSERT INTO se_ctr_remdat
  (SELECT DISTINCT 0 remdat_id,
          'EUR' devise_cod,
          'NATA' remcod_cod,
          NULL promo_cod,
          0 enreg_id,
          Nvl(MARQUE,1) marque_cod,
          client client_cod,
          'N' enreg_s,
          datdeb debut_dat,
          Max(datfin) fin_dat,
          NULL debliv_dat,
          NULL finliv_dat

  FROM (
  SELECT r.*,rcc.cclasse,rc.classe,To_Date('31/12/1967','dd/mm/yyyy')+rdd.datdeb datdeb,To_Date('31/12/1967','dd/mm/yyyy')+rdf.datfin datfin,rex.remex,rsu.remsup
  FROM uv_cremsup r,uv_cremsup_cclasse rcc ,uv_cremsup_classe rc ,
      uv_cremsup_datdeb rdd,uv_cremsup_datfin rdf ,
      uv_cremsup_remex rex,uv_cremsup_remsup rsu
  WHERE r.id=rcc.id
    AND rc.id=rcc.id AND rc.RANGMULTI2=rcc.RANGMULTI2
    AND rdd.id=rc.id AND rdd.RANGMULTI2=rc.RANGMULTI2
    AND rdf.id=rdd.id AND rdf.RANGMULTI2=rdd.RANGMULTI2 AND rdf.RANGMULTI3=rdd.RANGMULTI3
    AND rex.id(+)=rdd.id AND rex.RANGMULTI2(+)=rdd.RANGMULTI2 AND rex.RANGMULTI3(+)=rdd.RANGMULTI3
    AND rsu.id(+)=rdd.id AND rsu.RANGMULTI2(+)=rdd.RANGMULTI2 AND rsu.RANGMULTI3(+)=rdd.RANGMULTI3


    )
    GROUP BY client,marque,datdeb
  )   ;


 UPDATE se_ctr_remdat tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;

 UPDATE se_ctr_remdat tf SET remdat_id=Nvl((select remdat_id FROM &erp.ctr_remdat f WHERE f.remcod_cod=tf.remcod_cod AND f.DEBUT_DAT=tf.debut_dat  AND f.devise_cod=tf.devise_cod  AND f.CLIENT_COD=tf.CLIENT_COD AND f.marque_cod=tf.marque_cod),0) ;
 UPDATE se_ctr_remdat tf SET remdat_id=&outils.ctr_remdat.NEXTVAL WHERE remdat_id IS NULL OR remdat_id =0 ;

 CALL uvse_synchroverserp('remdat_id','CTR_REMDAT','O') ;

 commit;


